package mailserver.sql;

import java.sql.*;
import java.util.*;
import mailcommon.entities.User;
import mailserver.server.exceptions.UserExistsException;

public class UserService {

    private static UserService instance = null;

    private UserService() {
    }

    public static UserService getInstance() {
        if (instance == null) {
            instance = new UserService();
        }
        return instance;
    }

    private boolean exists(User user)
            throws SQLException {
        ArrayList<User> check = select(""
                + "lastname=\"" + user.getLastname() + "\" and "
                + "firstname=\"" + user.getFirstname() + "\" and "
                + "birthdate=\"" + user.getBirthdateString() + "\" and "
                + "telephone=\"" + user.getTelephone() + "\"");
        return !check.isEmpty();
    }

    public void add(User user)
            throws SQLException, UserExistsException {

        if (exists(user))  {
            throw new UserExistsException();
        }

        String sql = "insert into "
                + "user(lastname,firstname,birthdate,telephone) "
                + "values ("
                + "\"" + user.getLastname() + "\", "
                + "\"" + user.getFirstname() + "\", "
                + "\"" + user.getBirthdateString() + "\", "
                + "\"" + user.getTelephone() + "\""
                + ")";

        Connection con = DBController.getInstance().createConnection();
        try {
            Statement stmt = con.prepareStatement(sql);
            stmt.execute(sql);
            stmt.close();
        } finally {
            con.close();
        }
    }

    public ArrayList<User> select(String whereParams)
            throws SQLException {
        ArrayList<User> users = new ArrayList<User>();

        String query = "SELECT * "
                + " FROM user"
                + (!whereParams.isEmpty() ? " WHERE " + whereParams : "")
                + ";";

        Connection con = DBController.getInstance().createConnection();
        try {
            PreparedStatement stmt = con.prepareStatement(query);
            ResultSet rs = stmt.executeQuery();

            User cur;
            while (rs.next()) {
                cur = new User(rs.getString("lastname"),
                        rs.getString("firstname"),
                        rs.getDate("birthdate").toString(),
                        rs.getString("telephone"),
                        rs.getInt("id"));
                users.add(cur);
            }

            stmt.close();
        } finally {
            con.close();
        }
        return users;
    }

    public int getId(User user)
            throws SQLException {
        ArrayList<User> selected = select(
                "lastname = \"" + user.getLastname() + "\" and "
                + "firstname = \"" + user.getFirstname() + "\" and "
                + "birthdate = \"" + user.getBirthdateString() + "\" and "
                + "telephone = " + user.getTelephone());
        return selected.get(0).getId();
    }
}
